package com.letu.android.widget; 

import android.content.ComponentName;
import android.content.Context;
import android.content.ContentValues;
import android.content.Intent;
import android.content.pm.PackageManager;
import android.content.pm.ResolveInfo;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.Uri;
import android.util.Log;

import java.lang.Integer;
import java.net.URISyntaxException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;

public class Database extends SQLiteOpenHelper {

    private static final String LOG_TAG = "Database";
    private static final String DATABASE_NAME = "letu.db";
    private static final int DATABASE_VERSION = 2;

    private static final String TABLE_CITY = "city";

    private static final String PROVINCE_ID= "province_id";
    private static final String CITY_ID = "city_id";
    private static final String CITY_NAME = "city_name";
    
    private static final String TABLE_COLLECTION = "collection";
    private static final String ID = "id";
    private static final String COLLECTION_ID = "collection_id";
    private static final String COLLECTION_TITLE = "collection_title";
    private static final String COLLECTION_SUB_TYPE = "collection_sub_type";
    private static final String COLLECTION_MAIN_TYPE = "collection_main_type";
    
    private static final String TABLE_MONTH_RECOMMEND = "month_recommend";
    private static final String MONTH_RECOMMEND_ID = "month_recommend_id";
    private static final String MONTH_RECOMMEND_TITLE = "month_recommend_title";
    

    private Context mContext;

    public Database(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.mContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        try {
			Log.d(LOG_TAG, "database oncreate");
            db.execSQL("CREATE TABLE city(" +
                    "city_id INTEGER NOT NULL PRIMARY KEY," +
                    "city_name TEXT NOT NULL," +
                    "province_id INTEGER NOT NULL" +
                    ");");
            
            db.execSQL("CREATE TABLE collection(" +
                    "id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ," +
                    "collection_id TEXT NOT NULL," +
                    "collection_title TEXT NOT NULL," +
                    "collection_sub_type INTEGER DEFAULT 0," +
                    "collection_main_type INTEGER NOT NULL" +
                    ");");
            
            db.execSQL("CREATE TABLE month_recommend(" +
                    "id INTEGER NOT NULL PRIMARY KEY," +
                    "month_recommend_id TEXT NOT NULL," +
                    "month_recommend_title TEXT NOT NULL" +
                    ");");
            
    		initCityData(db);

        } catch (SQLException e) {
			 Log.d(LOG_TAG, "database exception");
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 
        if (oldVersion != DATABASE_VERSION) {
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_CITY);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_COLLECTION);
            onCreate(db);
        }
    }

    public void initProvinceData() {
    }

    public void initCityData(SQLiteDatabase db) {
    	
		String cityIds[] = mContext.getResources().getStringArray(R.array.city_id);
		String cityNames[] = mContext.getResources().getStringArray(R.array.city_name);
		String cityProvinces[] = mContext.getResources().getStringArray(R.array.city_province_id);

		Log.d(LOG_TAG, "init city 1");
		String cols[] = {CITY_ID};
		Cursor cursor = db.query(TABLE_CITY, cols, null, null, null, null, null);
		try {
			int rowNum = cursor.getCount();
			if (rowNum > 0) {
				return;
			}
		} finally {
			cursor.close();
		}

		for (int i = 0; i < cityIds.length; i++) {
        	ContentValues values = new ContentValues();
       		values.put(CITY_ID, cityIds[i]);
        	values.put(CITY_NAME, cityNames[i]);
       		values.put(PROVINCE_ID, cityProvinces[i]);
       		db.insert(TABLE_CITY, null, values);
		}
    }
    
    public String[][] getCityNameAndId(String cityProvinceId) {
        SQLiteDatabase db = getReadableDatabase();
        String cols[] = { CITY_ID, CITY_NAME };
		String[][] citys;
		String where = PROVINCE_ID + "=" + cityProvinceId;
        Cursor cursor = db.query(TABLE_CITY, cols, where, null, null, null, null); 
        try {
            int rowNum = cursor.getCount();
            if (rowNum <= 0) {
                return null;
            }
			
			citys = new String[2][rowNum];
            int i = 0;
            while (cursor.moveToNext()) {
                citys[0][i] = cursor.getString(0);
                citys[1][i] = cursor.getString(1);
                ++i;
            }
        } finally {
            cursor.close();
            db.close();
        }
        return citys;
    }

	public boolean insertCollectionToDB(String id, String title, int subType, int mainType) {
        SQLiteDatabase db = getWritableDatabase(); 
        ContentValues values = new ContentValues();
        
        values.put(COLLECTION_ID, id); 
        values.put(COLLECTION_TITLE, title); 
        values.put(COLLECTION_SUB_TYPE, subType);
        values.put(COLLECTION_MAIN_TYPE, mainType);
        
        String cols[] = {COLLECTION_ID, COLLECTION_SUB_TYPE, COLLECTION_MAIN_TYPE};
        String where = COLLECTION_ID + "=" + id + " and " + COLLECTION_SUB_TYPE + "=" + subType + 
        	" and " + COLLECTION_MAIN_TYPE + "=" + mainType;
        Cursor cursor = db.query(TABLE_COLLECTION, cols, where, null, null, null, null); 
        int rowNums = cursor.getCount();
        if (rowNums > 0) {
            cursor.close();
            db.close();
            return false;
        }
        cursor.close();
        boolean ret = db.insert(TABLE_COLLECTION, null, values) > 0;
        db.close();
        return ret;
    }
	
	public String[][] getCollectionFromDB() {
        SQLiteDatabase db = getReadableDatabase();
        String cols[] = { COLLECTION_ID, COLLECTION_TITLE, COLLECTION_SUB_TYPE, COLLECTION_MAIN_TYPE};
		String[][] records;
        Cursor cursor = db.query(TABLE_COLLECTION, cols, null, null, null, null, null); 
        try {
            int rowNum = cursor.getCount();
            if (rowNum <= 0) {
                return null;
            }
			
			records = new String[4][rowNum];
            int i = 0;
            while (cursor.moveToNext()) {
            	records[0][i] = cursor.getString(0);
            	records[1][i] = cursor.getString(1);
            	records[2][i] = cursor.getString(2);
            	records[3][i] = cursor.getString(3);
                ++i;
            }
        } finally {
            cursor.close();
            db.close();
        }
        return records;
    }
	
    public boolean deleteCollectionFromDB(String id, int subType, int mainType) {
        SQLiteDatabase db = getWritableDatabase(); 
        String where = COLLECTION_ID + "=" + id + " and " + COLLECTION_SUB_TYPE + "=" + subType
        		+ " and " + COLLECTION_MAIN_TYPE + "=" + mainType;
        boolean ret = db.delete(TABLE_COLLECTION, where, null) > 0;
        db.close();
        return ret;
    }
    
    public boolean insertMonthRecommend(String [][] monthRecommendContent) {    	
    	deleteMonthRecommend();
    	
    	SQLiteDatabase db = getWritableDatabase();
    	for (int i = 0; i < monthRecommendContent[0].length; i++) {    		
            ContentValues values = new ContentValues();            
            values.put(MONTH_RECOMMEND_ID, monthRecommendContent[0][i]); 
            values.put(MONTH_RECOMMEND_TITLE, monthRecommendContent[1][i]);
            db.insert(TABLE_MONTH_RECOMMEND, null, values);
    	}
    	db.close();
    	return true;
    }
    
    public boolean deleteMonthRecommend() {
        SQLiteDatabase db = getWritableDatabase();
        boolean ret = db.delete(TABLE_MONTH_RECOMMEND, null, null) > 0;
        db.close();
        return ret;
    }
    
    public String[][] getMonthRecommendFromDB() {
        SQLiteDatabase db = getReadableDatabase();
        String cols[] = { MONTH_RECOMMEND_ID, MONTH_RECOMMEND_TITLE };
		String[][] records = new String[2][];
        Cursor cursor = db.query(TABLE_MONTH_RECOMMEND, cols, null, null, null, null, null); 
        try {
            int rowNum = cursor.getCount();
            if (rowNum > 0) {			
            	records = new String[2][rowNum];
            	int i = 0;
            	while (cursor.moveToNext()) {
            		records[0][i] = cursor.getString(0);
            		records[1][i] = cursor.getString(1);
            		++i;
            	}
            }
        } finally {
            cursor.close();
            db.close();
        }
        return records;
    }

    /*

    public void syncPackage() {
        deleteOverMonthRecord();

        final Intent mainIntent = new Intent(Intent.ACTION_MAIN, null);
        mainIntent.addCategory(Intent.CATEGORY_LAUNCHER);
        PackageManager packageManager = context.getPackageManager();
        List<ResolveInfo> infos = packageManager.queryIntentActivities(mainIntent, 0);
        if (infos.isEmpty()) {
            return;
        }
        
        SQLiteDatabase db = getWritableDatabase(); 
        StringBuffer buf = new StringBuffer(PACKAGE);
        buf.append(" not in (");
        for (ResolveInfo data : infos) {
            buf.append("'");
            buf.append(data.activityInfo.packageName);
            buf.append("',");
        }
        buf.deleteCharAt(buf.length() - 1);
        buf.append(")");
        String where = buf.toString();

        db.delete(TABLE_RECENT, where, null);
        db.delete(TABLE_FREQUENCY, where, null);        
        db.close();
    }
	*/
	
}

